# -*- coding: utf-8 -*-
#autor: Sergio Sardo
#data : 28 de outubro de 2013
#obs  : Programa que importa dados de ficheiro xls e grava numa bd

from xlrd import open_workbook
import csv
import sqlite3

import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer, Float
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Sequence
from sqlalchemy.sql import func
from sqlalchemy.sql import label
import matplotlib.pyplot as plt
import matplotlib.cbook as cbook
from matplotlib.mlab import csv2rec
import numpy as np

'''
Abrir livroXLS, no primeiro Book
'''
book = open_workbook('cna131fresultados.xls')
print book
sheet = book.sheet_by_index(0)

print "Nome do Livro: ", sheet.name
print "Linhas: ", sheet.nrows
print "Colunas: ", sheet.ncols
print"------------------------------"

'''
lista e uma lista completa dos dados da BD
lista_aux e uma lista auxiliar que guarda uma linha por iteracao
'''
lista = []
lista_aux = []

'''
percorre toda a folha de calculo, linha a linha
guarda os valores de cada celula numa lista auxiliar lista_aux
guarda em cada iteracao, uma nova linha na lista "lista"
'''
for row_index in range(sheet.nrows):
    for col_index in range(sheet.ncols):
        u = sheet.cell(row_index, col_index).value
        lista_aux.append(u)
    lista.append(lista_aux)
    lista_aux = []
    
'''
lista_final contem os dados da folhaXLS filtrados, elimina dados nao uteis
'''
lista_final =[]

'''
filtro de dados uteis. remove cabeçalhos da folhaXLS e somatorios
'''
for linhas in range(3,len(lista)):
    if (lista[linhas][2] != ''):
        lista_final.append(lista[linhas])

print "Linhas db: ",len(lista_final)
print "Colunas db: ",len(lista_final[0])


engine = create_engine('sqlite:///alchemy_db_final.db', echo=True)
Base = declarative_base()

class Dados(Base):
    __tablename__ = 'dados'

    id = Column(Integer, Sequence('user_id_seq'), primary_key=True)
    cod_inst = Column(Integer)
    cod_curso = Column(Integer)
    nome = Column(String)
    curso = Column(String)
    grau = Column(String)
    vagas = Column(Integer)
    colocados = Column(Integer)
    nota_ultimo = Column(Integer)
    vagas_sob = Column(Integer)
    cod_distrito = Column(Integer)

'''
    def __init__(self, cod_inst, cod_curso, nome, curso, grau, vagas,
                 colocados, nota_ultimo, vagas_sob):
        self.cod_inst = cod_inst
        self.cod_curso = cod_curso
        self.nome = nome
        self.curso = curso
        self.grau = grau
        self.vagas = vagas
        self.colocados = colocados
        self.nota_ultimo = nota_ultimo
        self.vagas_sob = vagas_sob
'''

Base.metadata.create_all(engine)
#conn = engine.connect()

Session = sessionmaker(bind=engine)
session = Session()

'''
for x in lista_final:
    user = Dados(cod_inst = x[0],cod_curso = x[1], nome = x[2], curso = x[3], grau = x[4],
                vagas = x[5], colocados = x[6], nota_ultimo = x[7], vagas_sob = x[8])
    session.add(user)
session.commit()
'''
'''
primeiro exercicio de estatistica. colocados por instituicao
'''
a = session.query(Dados.cod_inst, Dados.nome, label('soma',func.sum(Dados.colocados))).group_by(Dados.cod_inst).all() # Alunos colocados por instituição

# Listas de cod_insts por Distrito
acores = [110, 130, 7090, 7091]
aveiro = [300, 3011, 3012, 3013, 3014]
braga = [1000, 3031, 3032, 7010]
braganca = [3041, 3042, 3043, 3045, 7015]
beja = [3021, 3022, 3023, 7005]
castelobranco = [400, 3051, 3052, 3053, 3054, 3055, 7020]
coimbra = [501, 502, 503, 504, 505, 506, 507, 508, 3061, 3062, 3063,3064, 3065, 7001, 7210]
evora = [602, 603, 604, 7030]
faro = [200, 201, 202, 203, 204, 3081, 3082, 3083,3087, 7035]
guarda = [3091, 3092, 3095, 7040]
leiria = [3101, 3102, 3103, 3105, 7045]
lisboa = [700, 701, 702, 703, 704, 705, 708, 709, 710, 801,802, 803, 804, 805, 806, 807, 808, 901, 902,903, 904, 906, 911, 3112, 3113, 3117, 3118,5302, 6600, 6800, 7002, 7105, 7110, 7220]
madeira = [1300]
portalegre = [3121, 3122, 3123, 7055]
porto = [1101, 1102, 1103, 1104, 1105, 1106, 1107, 1108, 1109, 1110, 1111, 1113,1114, 3131, 3132, 3133, 3134, 3135, 3138, 5402, 7003, 7230]
santarem = [3141, 3142, 3143, 3145, 3241, 3242, 3243, 7065]
setubal = [3151, 3152, 3153, 3154, 3155]
vianadocastelo = [3161, 3162, 3163, 3164, 3165, 7075]
vilareal = [1201, 1202, 1203, 1204, 1205, 7080]
viseu = [3181, 3182, 3185, 3186, 7085]
nomes_distritos = ('Açores', 'Aveiro', 'Braga', 'Bragança', 'Beja', 'Castelo Branco', 'Coimbra', 'Évora', 'Faro', 'Guarda', 'Leiria', 'Lisboa',
                   'Madeira', 'Portalegre', 'Porto', 'Santarém', 'Setúbal', 'Viana do Castelo', 'Vila Real', 'Viseu')
#Fim das Listas de cod_insts por Distrito
b = session.query(Dados).all()
for x in b:
    if x.cod_inst in acores:
        x.cod_distrito = 1
    elif x.cod_inst in aveiro:
        x.cod_distrito = 2
    elif x.cod_inst in braga:
        x.cod_distrito = 3
    elif x.cod_inst in braganca:
        x.cod_distrito = 4
    elif x.cod_inst in beja:
        x.cod_distrito = 5
    elif x.cod_inst in castelobranco:
        x.cod_distrito = 6
    elif x.cod_inst in coimbra:
        x.cod_distrito = 7
    elif x.cod_inst in evora:
        x.cod_distrito = 8
    elif x.cod_inst in faro:
        x.cod_distrito = 9
    elif x.cod_inst in guarda:
        x.cod_distrito = 10
    elif x.cod_inst in leiria:
        x.cod_distrito = 11
    elif x.cod_inst in lisboa:
        x.cod_distrito = 12
    elif x.cod_inst in madeira:
        x.cod_distrito = 13
    elif x.cod_inst in portalegre:
        x.cod_distrito = 14
    elif x.cod_inst in porto:
        x.cod_distrito = 15
    elif x.cod_inst in santarem:
        x.cod_distrito = 16
    elif x.cod_inst in setubal:
        x.cod_distrito = 17
    elif x.cod_inst in vianadocastelo:
        x.cod_distrito = 18
    elif x.cod_inst in vilareal:
        x.cod_distrito = 19
    elif x.cod_inst in viseu:
        x.cod_distrito = 20
        
session.commit()

c = session.query(Dados.cod_distrito, label('soma', func.sum(Dados.colocados))).group_by(Dados.cod_distrito).all() # Alunos colocados por Distrito

total_colocados = (session.query(func.sum(Dados.colocados)).all())[0][0] # Total de alunos colocados a nível nacional

d = c

# Calculo da permilagem dos estudantes colocados por distrito - colocados / total_colocados * 1000
calc_aux = []
for x in d:
    z = (x.cod_distrito, float(x.soma) / float(total_colocados) * float(1000))
    calc_aux.append(z)

# Percentagem de alunos colocados por instituição em relação a todos os alunos colocados
e = a
calc_auxB = []
z = 0
for x in e:
    z = (x.cod_inst,x.nome.encode('utf-8'), float(x.soma) / float(total_colocados) * float(100))
    calc_auxB.append(z)
# Calculo de numero de vagas por colocar por instituição
f = session.query(Dados.cod_inst,Dados.nome, label('soma', func.sum(Dados.vagas_sob))).group_by(Dados.cod_inst).all()

#Calculo de numero de vagas por colocar por distrito
g = session.query(Dados.cod_distrito, label('soma', func.sum(Dados.vagas_sob))).group_by(Dados.cod_distrito).all()
'''
Codigo para gerar CSV files
'''
#Número de Alunos colocados por instituição
aa = []
aa.append(('cod_inst', 'nome', 'colocados'))
for x in a:
    temp1 = x.cod_inst
    temp2 = x.nome.encode('utf-8')
    temp3 = x.soma
    temps = (temp1, temp2, temp3)
    aa.append(temps)
        
with open('alunosporinstituicao.csv', 'wb') as csvfile:
    escritor = csv.writer(csvfile)
    escritor.writerows(aa)
#Número de Alunos colocados por Distrito
with open('alunospordistrito.csv', 'wb') as csvfile:
    escritor = csv.writer(csvfile)
    escritor.writerows(c)
#Permilagem de alunos colocados por distrito
with open('permilagem.csv', 'wb') as csvfile:
    escritor = csv.writer(csvfile)
    escritor.writerows(calc_aux)
#Percentagem de Alunos Colocados por instituição
with open('percentagem_colocados.csv', 'wb') as csvfile:
    escritor = csv.writer(csvfile)
    escritor.writerows(calc_auxB)
#Número de vagas por colocar por instituição
aa = []
aa.append(('cod_inst', 'nome', 'vagas sobrantes'))
for x in f:
    temp1 = x.cod_inst
    temp2 = x.nome.encode('utf-8')
    temp3 = x.soma
    temps2 = (temp1, temp2, temp3)
    aa.append(temps2)
    
with open('vagassobrantes_inst.csv', 'wb') as csvfile:
    escritor = csv.writer(csvfile)
    escritor.writerows(aa)
    
#Número de vagas por colocar por distrito
with open('vagassobrantes_distrito.csv', 'wb') as csvfile:
    escritor = csv.writer(csvfile)
    escritor.writerows(g)
'''
Gerar Gráficos a partir dos ficheiros CSV

'''
# Gráfico para alunos colocados por instituição
aX = []
aNames = []
for x in a:
    aX.append(x.soma)

for x in a:
    aNames.append(x.nome)
#x_pos = np.arange(len(aNames))
#plt.xticks(x_pos,aNames)
plt.plot(aX)
plt.show()

#Gráfico para alunos colocados por Distrito
aX = []
aValor = []
for x in c:
    aX.append(x.soma)

for x in c:
    aNames.append(x.cod_distrito)

#x_pos = np.arange(len(aNames))
#plt.xticks(x_pos,aNames)
plt.plot(aX)
plt.show()

#Gráfico permilagem de alunos colocados por distrito
aX = []

for x in calc_aux:
    aX.append(x[1])
plt.plot(aX)
plt.show()
